Failed to create a function - Mailing list pgsql-novice

Hi guys,

OK, I'm able to get postmaster running, create and drop a db, create
and drop tables.

Now I'm trying to create a function (for a trigger) which is a port
from an oracle stored procedure, and I don't understand why it fails
since the syntax appears to be correct from the book examples.

These are the three dollops of sql that I've been pasting in...

create table WDRole (
    WDRoleID integer,
    name varchar(16),
    primary key (WDRoleID)
);

...which goes in ok, then...

create table WDVolume (
    WDVolumeID integer,
    Mountpoint varchar(255),
    Name varchar(255),
    Readable char(1),
    Writeable char(1),
    DiskAllocated integer,
    DiskUsed integer,
    DiskAvailPC decimal(5,2),
    TotalFiles integer,
    LastFileSeq integer,
    primary key (WDVolumeID)
);

...which goes in ok, then...

CREATE FUNCTION trigger_WDVolume () RETURNS opaque AS '
  DECLARE
   -- set the default disk space
   disk_avail CONSTANT integer := 100;

  BEGIN
   IF OLD.DiskUsed is null THEN
    NEW.DiskAvailPC := disk_avail;
   ELSE
    NEW.DiskAvailPC := disk_avail - (disk_avail * OLD.DiskUsed) /
OLD.DiskAllocated;
   ENDIF;

   RETURN NEW;
  END
' LANGUAGE 'plpgsql';


...and this is the tty session which turns to poo...

Last login: Tue Nov 25 12:58:30 on ttyp1
Welcome to Darwin!
[MacGregor:~] roy% su postgres
Password:
[MacGregor:/Users/roy] postgres% pg_ctl  -D /usr/local/pgsql/data status
pg_ctl: postmaster is running (pid: 456)
Command line was:
/usr/local/bin/postmaster
[MacGregor:/Users/roy] postgres% psql template1
Welcome to psql 7.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help on internal slash commands
        \g or terminate with semicolon to execute query
        \q to quit

template1=# create database webdav;
CREATE DATABASE
template1-# \q
[MacGregor:/Users/roy] postgres% psql webdav
Welcome to psql 7.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help on internal slash commands
        \g or terminate with semicolon to execute query
        \q to quit

webdav=# create table WDRole (
webdav(# WDRoleID integer,
webdav(# name varchar(16),
webdav(# primary key (WDRoleID)
webdav(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'wdrole_pkey' for table 'wdrole'
CREATE TABLE
webdav=# create table WDVolume (
webdav(# WDVolumeID integer,
webdav(# Mountpoint varchar(255),
webdav(# Name varchar(255),
webdav(# Readable char(1),
webdav(# Writeable char(1),
webdav(# DiskAllocated integer,
webdav(# DiskUsed integer,
webdav(# DiskAvailPC decimal(5,2),
webdav(# TotalFiles integer,
webdav(# LastFileSeq integer,
webdav(# primary key (WDVolumeID)
webdav(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'wdvolume_pkey' for table 'wdvolume'
CREATE TABLE
webdav=# CREATE FUNCTION trigger_WDVolume () RETURNS opaque AS '
webdav'# DECLARE
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'# -- set the default disk space
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'# disk_avail CONSTANT integer := 100;
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'#
webdav'# BEGIN
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'# IF OLD.DiskUsed is null THEN
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'# NEW.DiskAvailPC := disk_avail;

webdav'# NEW.DiskAvailPC := disk_avail;

webdav'# NEW.DiskAvailPC := disk_avail;
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'# ELSE
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'# NEW.DiskAvailPC := disk_avail - (disk_avail * OLD.DiskUsed)
/ OLD.DiskAllocated;

webdav'# NEW.DiskAvailPC := disk_avail - (disk_avail * OLD.DiskUsed)
/ OLD.DiskAllocated;

webdav'# NEW.DiskAvailPC := disk_avail - (disk_avail * OLD.DiskUsed)
/ OLD.DiskAllocated;
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'# ENDIF;
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'#
webdav'#
ABORT     BEGIN     COMMENT   CREATE    DROP      GRANT     LOAD
NOTIFY    REVOKE    SET       UNLISTEN
ALTER     CLOSE     COMMIT    DECLARE   EXPLAIN   INSERT    LOCK
REINDEX   ROLLBACK  SHOW      UPDATE
ANALYZE   CLUSTER   COPY      DELETE    FETCH     LISTEN    MOVE
RESET     SELECT    TRUNCATE  VACUUM
webdav'# RETURN NEW;
webdav'# END
webdav'# ' LANGUAGE 'plpgsql';
ERROR:  language "plpgsql" does not exist
webdav=# \q


...so what doesn't it like here?


TIA,
Roy
--
--------------------
Roy MacGregor Paterson
MacGregorTech

t: +44 (0) 20 7584 7891
f: +44 (0) 20 7589 6223
m: +44 (0) 7803 163 938
w: www.macgregortech.com
e: roy@macgregortech.com
--------------------